package com.server;

import java.sql.Timestamp;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.mina.core.session.IdleStatus;
import org.apache.mina.core.service.IoHandlerAdapter;
import org.apache.mina.core.session.IoSession;
import com.server.constants.CONSTANTS;
import com.server.constants.CONSTANTS.ACCOUNT;
import com.server.constants.CONSTANTS.CLIENT_TRANSACTION;
import com.server.constants.CONSTANTS.JSON;
import com.server.constants.CONSTANTS.PRODUCT;
import com.server.constants.CONSTANTS.SERVER_TRANSACTION;
import com.server.constants.CONSTANTS.STATUS;
import com.server.db.Account;
import com.server.db.DatabaseManager;
import com.server.db.Product;
import com.server.helper.JSONHelper;
import com.server.json.JSONArray;
import com.server.json.JSONException;
import com.server.json.JSONObject;

public class ServerHandler extends IoHandlerAdapter
{
	private static Logger logger = Logger.getLogger("ServerHandler");

	@Override
	public void sessionOpened(IoSession session)
	{
		// set idle time to 10 seconds
		session.getConfig().setIdleTime(IdleStatus.BOTH_IDLE, 10);

		session.setAttribute("Values: ");
	}

	@Override
	public void messageReceived(IoSession session, Object message)
	{
		String messageReceived = (String) message;
		logger.info("Message received: " + messageReceived);

		if (messageReceived != null && !messageReceived.equals(""))
		{
			int status = processIncoming(session, messageReceived);

			if (status != STATUS.CLIENT_WAITING_RESPONSE)
			{
				JSONObject jsonObject = new JSONObject();
				try
				{
					jsonObject.put(JSON.KEY_TYPE + "",
							SERVER_TRANSACTION.REPLY_STATUS);
					jsonObject.put(CONSTANTS.JSON.KEY_VALUE + "", status);
					session.write(jsonObject.toString());
				} catch (JSONException e)
				{
					e.printStackTrace();
					logger.error(e.getMessage());
				}
				logger.info("Sending message TYPE STATUS = "
						+ jsonObject.toString());
				session.write(jsonObject.toString());
			}
		}
	}

	@SuppressWarnings("deprecation")
	@Override
	public void sessionIdle(IoSession session, IdleStatus status)
	{
		logger.info("Disconnecting the idle.");
		// disconnect an idle client
		session.close();
	}

	@SuppressWarnings("deprecation")
	@Override
	public void exceptionCaught(IoSession session, Throwable cause)
	{
		// close the connection on exceptional situation
		session.close();
	}

	@Override
	public void sessionCreated(IoSession session) throws Exception
	{
		// session.write("111111111111111111111111111");

		super.sessionCreated(session);
	}

	@Override
	public void messageSent(IoSession session, Object message) throws Exception
	{
		super.messageSent(session, message);
	}

	private int processOutcoming(IoSession session, int clientType)
	{
		JSONObject jsonObject = new JSONObject();
		JSONArray jsonArray = new JSONArray();

		int status = STATUS.SERVER_SEND_OK;

		switch (clientType)
		{
			case CLIENT_TRANSACTION.ACCOUNT:

				logger.info("Message type requet to table = "
						+ CONSTANTS.ACCOUNT.TABLE_NAME);

				List<Account> accountsList = DatabaseManager.getAllAccounts();

				if (accountsList == null)
				{
					logger.error("Get database data fail");
					status = STATUS.SERVER_SEND_FAIL_DB;
					break;
				}

				try
				{
					JSONObject jsonObjectElement = null;
					for (Account account : accountsList)
					{
						jsonObjectElement = new JSONObject();

						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_ID + "",
								account.getId());
						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_AMOUNT + "",
								account.getAmount());

						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_ACTIVE + "",
								account.isActive() ? 1 : 0);

						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_DATE_CREATED
								+ "", account.getDateCreated());

						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_GROUP_ID
								+ "", account.getGroupId());

						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_PASSWORD
								+ "", account.getPassword());

						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_PIN_CODE
								+ "", account.getPinCode());

						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_POSITION_ID
								+ "", account.getPositionId());
						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_STAFF_ID
								+ "", account.getStaffId());
						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_TERMINATED
								+ "", account.isTerminated() ? 1 : 0);
						jsonObjectElement.put(ACCOUNT.COLUMN_INDEX_USER_NAME
								+ "", account.getUsername());

						jsonArray.put(jsonObjectElement);
					}

				} catch (JSONException e)
				{
					e.printStackTrace();
					logger.error(e.getMessage());
				} catch (Exception e)
				{
					e.printStackTrace();
					logger.error(e.getMessage());
				}

				break;

			case CLIENT_TRANSACTION.PRODUCT:
				logger.info("Message type requet to table = "
						+ CONSTANTS.PRODUCT.TABLE_NAME);

				List<Product> productsList = DatabaseManager.getAllProducts();

				if (productsList == null)
				{
					logger.error("Get database data fail");
					status = STATUS.SERVER_SEND_FAIL_DB;
					break;
				}

				try
				{
					JSONObject jsonObjectElement = null;
					for (Product product : productsList)
					{
						jsonObjectElement = new JSONObject();

						jsonObjectElement.put(
								PRODUCT.COLUMN_INDEX_IMAGE_PATH + "",
								product.getImageName());

						jsonObjectElement.put(
								PRODUCT.COLUMN_INDEX_PRICE_BUYING + "",
								product.getPriceBuy());

						jsonObjectElement.put(
								PRODUCT.COLUMN_INDEX_PRICE_SELLING + "",
								product.getPriceSell());

						jsonObjectElement.put(
								PRODUCT.COLUMN_INDEX_CODE + "",
								product.getCode());

						jsonObjectElement.put(
								PRODUCT.COLUMN_INDEX_GROUP_ID + "",
								product.getGroupId());

						jsonObjectElement.put(
								PRODUCT.COLUMN_INDEX_NAME + "",
								product.getName());

						// jsonObjectElement.put(
						// PRODUCT.COLUMN_INDEX_PRODUCT_SUPPLIER_ID + "",
						// resultSet.getInt(CONSTANTS.PRODUCT.COLUMN_PRODUCT_SUPPLIER_ID));

						jsonArray.put(jsonObjectElement);
					}
				}catch (JSONException e)
				{
					e.printStackTrace();
					logger.error(e.getMessage());
				}

				break;

			default:
				break;
		}

		
		try
		{
			jsonObject = new JSONObject();
			if (status == STATUS.SERVER_SEND_FAIL_DB)
			{
				jsonObject.put(JSON.KEY_TYPE + "", SERVER_TRANSACTION.ERROR);
				jsonObject.put(CONSTANTS.JSON.KEY_VALUE + "",
						STATUS.SERVER_SEND_FAIL_DB + "");
			}
			else if (status == STATUS.SERVER_SEND_FAIL_DB_LOAD)
			{
				jsonObject.put(JSON.KEY_TYPE + "", SERVER_TRANSACTION.ERROR);
				jsonObject.put(CONSTANTS.JSON.KEY_VALUE + "",
						STATUS.SERVER_SEND_FAIL_DB_LOAD + "");
			}
			else
			{
				if (clientType == CLIENT_TRANSACTION.ACCOUNT) jsonObject.put(
						JSON.KEY_TYPE + "", SERVER_TRANSACTION.ACCOUNT);
				else jsonObject.put(JSON.KEY_TYPE + "",
						SERVER_TRANSACTION.PRODUCT);
				jsonObject.put(CONSTANTS.JSON.KEY_VALUE + "",
						jsonArray.toString());
			}
		} catch (Exception e)
		{
			e.printStackTrace();
			logger.error(e.getMessage());
		}
		logger.info("Sending message TYPE PRODUCT-ACCOUNT = "
				+ jsonObject.toString());
		session.write(jsonObject.toString());

		return STATUS.CLIENT_WAITING_RESPONSE;
	}

	private int processIncoming(IoSession session, String message)
	{
		JSONObject jsonObject = null;
		try
		{
			jsonObject = new JSONObject(message);
		} catch (JSONException e)
		{
			jsonObject = null;
			e.printStackTrace();

			logger.error("Load message fail!");
			logger.error(e.getMessage());
			return STATUS.CLIENT_SEND_FAIL_JSON;
		}

		int type = JSONHelper.getInt(jsonObject, CONSTANTS.JSON.KEY_TYPE + "");

		String value = null;
		value = JSONHelper.getString(jsonObject, CONSTANTS.JSON.KEY_VALUE + "");

		if (value == null || value.equals(""))
		{
			logger.error("Have no message value!");
			return STATUS.CLIENT_SEND_FAIL_VALUE_NULL;
		}

		JSONObject jsonObjectValue = null;
		if (type != CLIENT_TRANSACTION.UPDATE_DATA)
		{
			try
			{
				jsonObjectValue = new JSONObject(value);
			} catch (JSONException e)
			{
				jsonObjectValue = null;
				e.printStackTrace();

				logger.error("Load message value fail!");
				logger.error(e.getMessage());
				return STATUS.CLIENT_SEND_FAIL_JSON_VALUE;
			}
		}
		String columnSet = null;
		String columnValue = null;
		Timestamp timestamp = null;
		long time = 0;
		String tableName = null;

		boolean isCreateTableSuccess = false;
		boolean isInsertSuccess = false;

		switch (type)
		{
			case CLIENT_TRANSACTION.BOX_INVENTORY:
				logger.info("Message type insert to table = "
						+ CONSTANTS.BOX_INVENTORY.TABLE_NAME);

				// isCreateTableSuccess =
				// DatabaseUtils.createTableBoxInventory();

				tableName = CONSTANTS.BOX_INVENTORY.TABLE_NAME;

				columnSet = CONSTANTS.BOX_INVENTORY.COLUMN_AMOUNT + ", "
						+ CONSTANTS.BOX_INVENTORY.COLUMN_BOX_CODE + ", "
						+ CONSTANTS.BOX_INVENTORY.COLUMN_PRODUCT_CODE + ", "
						+ CONSTANTS.BOX_INVENTORY.COLUMN_MACHINE_CODE;

				columnValue = JSONHelper.getInt(jsonObjectValue,
						CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_AMOUNT + "")
						+ ","

						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_BOX_CODE
										+ "")

						+ ",'"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_PRODUCT_CODE
										+ "")

						+ "','"
						+ JSONHelper.getString(
								jsonObject,
								CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_MACHINE_CODE
										+ "") + "'";

				break;

			case CLIENT_TRANSACTION.MACHINE:

				logger.info("Message type update to table = "
						+ CONSTANTS.MACHINE.TABLE_NAME);

				// isCreateTableSuccess = DatabaseUtils.createTableMachine();

				tableName = CONSTANTS.MACHINE.TABLE_NAME;
				/*
				 * columnSet = CONSTANTS.MACHINE.COLUMN_AMOUNT_BOX + ", " +
				 * CONSTANTS.MACHINE.COLUMN_CODE + ", " +
				 * CONSTANTS.MACHINE.COLUMN_DATE_CREATED + ", " +
				 * CONSTANTS.MACHINE.COLUMN_DATE_TERMINATED + ", " +
				 * CONSTANTS.MACHINE.COLUMN_GROUP + ", " +
				 * CONSTANTS.MACHINE.COLUMN_LATITTUDE + ", " +
				 * CONSTANTS.MACHINE.COLUMN_LOCATION_ID + ", " +
				 * CONSTANTS.MACHINE.COLUMN_LONGITUDE + ", " +
				 * CONSTANTS.MACHINE.COLUMN_STAFF_CREATE + ", " +
				 * CONSTANTS.MACHINE.COLUMN_STAFF_TERMINATE + ", " +
				 * CONSTANTS.MACHINE.COLUMN_STAFF_UPDATE + ", " +
				 * CONSTANTS.MACHINE.COLUMN_STATUS + ", " +
				 * CONSTANTS.MACHINE.COLUMN_STATUS_RENT;
				 */
				time = 0;
				time = JSONHelper.getLong(jsonObjectValue,
						CONSTANTS.MACHINE.COLUMN_DATE_CREATED);

				if (time == 0) time = System.currentTimeMillis();
				Timestamp timestampCreated = new Timestamp(time);

				time = 0;
				time = JSONHelper.getLong(jsonObjectValue,
						CONSTANTS.MACHINE.COLUMN_DATE_TERMINATED);

				Timestamp timestampTerminated = null;
				if (time != 0) timestampTerminated = new Timestamp(time);
				/*
				 * int boxAmount = JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_AMOUNT_BOX + "");
				 * 
				 * if (boxAmount < 1) boxAmount = CONFIG.MAX_BOX_AMOUNT;
				 * 
				 * columnValue = boxAmount + ",'"
				 * 
				 * + JSONHelper.getString(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_CODE + "")
				 * 
				 * + "',?,?," + JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_GROUP + "") + "," +
				 * JSONHelper.getDouble(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_LATITTUDE + "") + "," +
				 * JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_LOCATION_ID + "") + "," +
				 * JSONHelper.getDouble(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_LONGITUDE + "") + ",'" +
				 * JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_STAFF_CREATE + "") + "','" +
				 * JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_STAFF_TERMINATE + "") + "','"
				 * + JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_STAFF_UPDATE + "") + "'," +
				 * JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_STATUS + "")
				 * 
				 * + "," + JSONHelper.getInt(jsonObjectValue,
				 * CONSTANTS.MACHINE.COLUMN_INDEX_STATUS_RENT + "");
				 * 
				 * long idMachineInsert = -1;
				 * 
				 * if (columnSet != null && columnValue != null && tableName !=
				 * null) { logger.info("Inserting...."); idMachineInsert =
				 * DatabaseManager.insertReturnId(tableName, columnSet,
				 * columnValue, new Timestamp[] { timestampCreated,
				 * timestampTerminated }); }
				 */

				// long idMachineInsert = -1;

				String valueSet = CONSTANTS.MACHINE.COLUMN_DATE_CREATED
						+ " = ?, "

						+ CONSTANTS.MACHINE.COLUMN_DATE_TERMINATED
						+ " = ?, "

						+ CONSTANTS.MACHINE.COLUMN_GROUP
						+ " = "
						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.MACHINE.COLUMN_INDEX_GROUP + "")
						+ ", "

						+ CONSTANTS.MACHINE.COLUMN_LATITTUDE
						+ " = "
						+ JSONHelper.getDouble(jsonObjectValue,
								CONSTANTS.MACHINE.COLUMN_INDEX_LATITTUDE + "")
						+ ", "

						+ CONSTANTS.MACHINE.COLUMN_LOCATION_ID
						+ " = "
						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.MACHINE.COLUMN_INDEX_LOCATION_ID + "")
						+ ", "

						+ CONSTANTS.MACHINE.COLUMN_LONGITUDE
						+ " = "
						+ JSONHelper.getDouble(jsonObjectValue,
								CONSTANTS.MACHINE.COLUMN_INDEX_LONGITUDE + "")
						+ ", "

						// + CONSTANTS.MACHINE.COLUMN_STAFF_CREATE
						// + " = '"
						// + JSONHelper.getInt(jsonObjectValue,
						// CONSTANTS.MACHINE.COLUMN_INDEX_STAFF_CREATE
						// + "")
						// + "', "
						//
						// + CONSTANTS.MACHINE.COLUMN_STAFF_TERMINATE
						// + " = '"
						// + JSONHelper.getInt(jsonObjectValue,
						// CONSTANTS.MACHINE.COLUMN_INDEX_STAFF_TERMINATE
						// + "")
						// + "', "
						//
						// + CONSTANTS.MACHINE.COLUMN_STAFF_UPDATE
						// + " = '"
						// + JSONHelper.getInt(jsonObjectValue,
						// CONSTANTS.MACHINE.COLUMN_INDEX_STAFF_UPDATE
						// + "")
						// + "', "

						+ CONSTANTS.MACHINE.COLUMN_STATUS
						+ " = "
						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.MACHINE.COLUMN_INDEX_STATUS + "")
						+ ", "

						+ CONSTANTS.MACHINE.COLUMN_STATUS_RENT
						+ " = "
						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.MACHINE.COLUMN_INDEX_STATUS_RENT + "");

				String vmCode = JSONHelper.getString(jsonObjectValue,
						CONSTANTS.MACHINE.COLUMN_INDEX_CODE + "");

				String whereString = CONSTANTS.MACHINE.COLUMN_CODE + " = '"
						+ vmCode + "'";

				if (valueSet != null && tableName != null)
				{
					logger.info("Updating....");
					// idMachineInsert =
					DatabaseManager.updateReturnId(tableName, valueSet,
							new Timestamp[] { timestampCreated,
									timestampTerminated }, whereString);
				}

				// /* Request for machine id */
				// if (idMachineInsert == -1) {
				// logger.info("Error insert!!");
				// return STATUS.SERVER_DB_INSERT_FAIL;
				// }

				logger.info("Update successfully!!");
				// /* BOX PROCESS */
				// logger.info("Message type update to table = "
				// + CONSTANTS.BOX.TABLE_NAME);
				//
				// // isCreateTableSuccess = DatabaseUtils.createTableBox();
				// tableName = CONSTANTS.BOX.TABLE_NAME;
				//
				// columnSet = CONSTANTS.BOX.COLUMN_ACTIVE + ", "
				// + CONSTANTS.BOX.COLUMN_PRODUCT_AMOUNT + ", "
				// + CONSTANTS.BOX.COLUMN_MACHINE_CODE + ", "
				// + CONSTANTS.BOX.COLUMN_NAME + ", "
				// + CONSTANTS.BOX.COLUMN_PRODUCT_ID + ", "
				// + CONSTANTS.BOX.COLUMN_PRODUCT_PRICE;
				//
				// long idBoxInsert = -1;
				// String valueMachineAndBox = idMachineInsert + "";
				//
				// for (int i = 0; i < boxAmount; i++)
				// {
				// columnValue = "1, 0, " + idMachineInsert + ", '" + i
				// + "', 0, 0";
				//
				// if (columnSet != null && columnValue != null
				// && tableName != null)
				// {
				// logger.info("Inserting....");
				// idBoxInsert = DatabaseManager.insertReturnId(tableName,
				// columnSet, columnValue);
				// }
				//
				// valueMachineAndBox += ("," + idBoxInsert);
				// }
				//
				// /* Request for box ids */
				// // if (idBoxInsert == -1) return STATUS.DB_INSERT_FAIL;
				//
				// // JSONObject jsonObjectMachine = new JSONObject();
				// JSONObject jsonObjectMachineValue = new JSONObject();
				//
				// try
				// {
				// // jsonObjectMachine.put(JSON.KEY_TYPE + "",
				// // SERVER_TRANSACTION.MACHINE);
				//
				// jsonObjectMachineValue.put(JSON.KEY_TYPE + "",
				// SERVER_TRANSACTION.MACHINE);
				// jsonObjectMachineValue.put(CONSTANTS.JSON.KEY_VALUE + "",
				// valueMachineAndBox);
				//
				// // jsonObjectMachine.put(CONSTANTS.JSON.KEY_VALUE + "",
				// // jsonObjectMachineValue.toString());
				//
				// } catch (Exception e)
				// {
				// e.printStackTrace();
				// logger.error(e.getMessage());
				// }
				//
				// String messageSend = jsonObjectMachineValue.toString();
				//
				// logger.info("Sending message TYPE Machine = " + messageSend);
				//
				// session.write(messageSend);

				int totalBox = DatabaseManager.getBoxAmount(CONSTANTS.MACHINE.COLUMN_CODE
						+ " = '" + vmCode + "'");

				logger.info("Total box is : " + totalBox);

				JSONObject jsonObjectMachineValue = new JSONObject();

				try
				{
					jsonObjectMachineValue.put(JSON.KEY_TYPE + "",
							SERVER_TRANSACTION.MACHINE);
					jsonObjectMachineValue.put(CONSTANTS.JSON.KEY_VALUE + "",
							totalBox);

				} catch (Exception e)
				{
					e.printStackTrace();
					logger.error(e.getMessage());
				}

				String messageSend = jsonObjectMachineValue.toString();

				logger.info("Sending message TYPE Machine = " + messageSend);

				session.write(messageSend);

				return STATUS.CLIENT_WAITING_RESPONSE;

			case CLIENT_TRANSACTION.ACCOUNT:
				/* Request for accounts */
			case CLIENT_TRANSACTION.PRODUCT:
				/* Request for products */
				return processOutcoming(session, type);

			case CLIENT_TRANSACTION.PRODUCT_PUTIN:

				logger.info("Message type insert to table = "
						+ CONSTANTS.PRODUCT_PUT_IN.TABLE_NAME);

				// isCreateTableSuccess =
				// DatabaseUtils.createTableProductPutIn();

				tableName = CONSTANTS.PRODUCT_PUT_IN.TABLE_NAME;

				columnSet = CONSTANTS.PRODUCT_PUT_IN.COLUMN_AMOUNT + ", "
						+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_BOX_CODE + ", "
						+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_PRODUCT_CODE + ", "
						+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_DATE + ", "
						+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_MACHINE_CODE + ", "
						+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_STAFF_CHECKER + ", "
						+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_STAFF_SERVICE;

				time = JSONHelper.getLong(jsonObjectValue,
						CONSTANTS.PRODUCT_PUT_IN.COLUMN_DATE);

				if (time == 0) time = System.currentTimeMillis();

				timestamp = new Timestamp(time);

				columnValue = JSONHelper.getInt(jsonObjectValue,
						CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_AMOUNT + "")
						+ ","

						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_BOX_CODE
										+ "")

						+ ",'"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_PRODUCT_CODE
										+ "")
						+ "', ?,"

						+ "'"
						+ JSONHelper.getString(
								jsonObject,
								CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_MACHINE_CODE
										+ "")
						+ "',"
						+

						"'"

						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_STAFF_CHECKER
										+ "")
						+ "','"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_STAFF_SERVICE
										+ "") + "'";

				break;
			case CLIENT_TRANSACTION.PRODUCT_TAKEOUT:

				logger.info("Message type insert to table = "
						+ CONSTANTS.PRODUCT_TAKE_OUT.TABLE_NAME);

				// isCreateTableSuccess =
				// DatabaseUtils.createTableProductTakeOut();

				tableName = CONSTANTS.PRODUCT_TAKE_OUT.TABLE_NAME;

				columnSet = CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_AMOUNT + ", "
						+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_BOX_CODE + ", "
						+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_PRODUCT_CODE + ", "
						+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_DATE + ", "
						+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_MACHINE_CODE + ", "
						+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_STAFF_CHECKER
						+ ", "
						+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_STAFF_SERVICE;

				time = JSONHelper.getLong(jsonObjectValue,
						CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_DATE);

				if (time == 0) time = System.currentTimeMillis();

				timestamp = new Timestamp(time);

				columnValue = JSONHelper.getInt(jsonObjectValue,
						CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_AMOUNT + "")
						+ ","

						+ JSONHelper.getInt(
								jsonObjectValue,
								CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_BOX_CODE
										+ "")

						+ ",'"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_PRODUCT_CODE
										+ "")
						+ "', ?,"
						+

						"'"
						+ JSONHelper.getString(
								jsonObject,
								CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_MACHINE_CODE
										+ "")
						+ "',"
						+

						"'"

						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_STAFF_CHECKER
										+ "")
						+ "','"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_STAFF_SERVICE
										+ "") + "'";
				break;
			case CLIENT_TRANSACTION.SELL:

				logger.info("Message type insert to table = "
						+ CONSTANTS.PRODUCT_SELL.TABLE_NAME);

				// isCreateTableSuccess =
				// DatabaseUtils.createTableProductSell();

				tableName = CONSTANTS.PRODUCT_SELL.TABLE_NAME;

				columnSet = CONSTANTS.PRODUCT_SELL.COLUMN_ACCOUNT_NUMBER + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_AMOUNT + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_BOX_CODE + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_PRODUCT_CODE + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_DATE + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_IS_DELIVERIED + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_MACHINE_CODE + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_PRICE + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_WORK_NUMBER1 + ", "
						+ CONSTANTS.PRODUCT_SELL.COLUMN_WORK_NUMBER2;

				time = JSONHelper.getLong(jsonObjectValue,
						CONSTANTS.PRODUCT_SELL.COLUMN_DATE);

				if (time == 0) time = System.currentTimeMillis();

				timestamp = new Timestamp(time);

				columnValue = "'"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_ACCOUNT_NUMBER
										+ "")
						+ "',"

						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_AMOUNT + "")

						+ ","
						+ JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_BOX_CODE
										+ "")
						+ ",'"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_PRODUCT_CODE
										+ "")
						+ "', ?,"

						+ JSONHelper.getInt(
								jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_IS_DELIVERIED
										+ "")
						+ ","
						+

						"'"
						+ JSONHelper.getString(
								jsonObject,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_MACHINE_CODE
										+ "")
						+ "',"
						+

						+JSONHelper.getInt(jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_PRICE + "")

						+ ",'"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_WORK_NUMBER1
										+ "")
						+ "',"

						+ "'"
						+ JSONHelper.getString(
								jsonObjectValue,
								CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_WORK_NUMBER2
										+ "") + "'";
				break;

			case CLIENT_TRANSACTION.UPDATE_DATA:

				logger.info("Message type updating all data ");
				JSONArray jsonArrayData = new JSONArray();
				try
				{
					jsonArrayData = new JSONArray(value);
				} catch (JSONException e)
				{
					jsonArrayData = null;
					e.printStackTrace();

					logger.error("Load message value fail!");
					logger.error(e.getMessage());
					return STATUS.CLIENT_SEND_FAIL_JSON_VALUE;
				}

				for (int typeIndex = 0; typeIndex < jsonArrayData.length(); typeIndex++)
				{
					try
					{
						JSONObject jsonObjectElement = jsonArrayData.getJSONObject(typeIndex);

						int subType = jsonObjectElement.getInt(JSON.KEY_TYPE
								+ "");

						String subValue = jsonObjectElement.getString(JSON.KEY_VALUE
								+ "");

						JSONArray jsonArraySubData = new JSONArray();
						try
						{
							jsonArraySubData = new JSONArray(subValue);
						} catch (JSONException e)
						{
							jsonArraySubData = null;
							e.printStackTrace();

							logger.error("Load message value fail!");
							logger.error(e.getMessage());
							return STATUS.CLIENT_SEND_FAIL_JSON_VALUE;
						}

						switch (subType)
						{
							case CLIENT_TRANSACTION.BOX:

								logger.info("Message type update to table = "
										+ CONSTANTS.BOX.TABLE_NAME);

								tableName = CONSTANTS.BOX.TABLE_NAME;

								for (int subIndex = 0; subIndex < jsonArraySubData.length(); subIndex++)
								{
									JSONObject jsonObjectSubElement = jsonArraySubData.getJSONObject(subIndex);

									String subValueSet =

									// CONSTANTS.BOX.COLUMN_ACTIVE
									// + " = "
									// + JSONHelper.getInt(
									// jsonObjectSubElement,
									// CONSTANTS.BOX.COLUMN_INDEX_ACTIVE
									// + "") + ", "
									//
									// +CONSTANTS.BOX.COLUMN_NAME
									// + " = '"
									// + JSONHelper.getString(
									// jsonObjectSubElement,
									// CONSTANTS.BOX.COLUMN_INDEX_NAME
									// + "") + "', "
									//
									// +

									CONSTANTS.BOX.COLUMN_PRODUCT_AMOUNT
											+ " = "
											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.BOX.COLUMN_INDEX_PRODUCT_AMOUNT
															+ "")
											+ "', "

											+ CONSTANTS.BOX.COLUMN_PRODUCT_CODE
											+ " = '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.BOX.COLUMN_INDEX_PRODUCT_CODE
															+ "")
											+ "', "

											+ CONSTANTS.BOX.COLUMN_PRODUCT_PRICE
											+ " = "
											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.BOX.COLUMN_INDEX_PRODUCT_PRICE
															+ "")

									;

									String subWhereString = CONSTANTS.BOX.COLUMN_ID
											+ " = '"
											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.BOX.COLUMN_INDEX_ID
															+ "") + "'";

									if (subValueSet != null
											&& tableName != null)
									{
										logger.info("Updating....");
										// idMachineInsert =

										DatabaseManager.updateReturnId(
												tableName, subValueSet, null,
												subWhereString);
									}
								}
								break;
							case CLIENT_TRANSACTION.BOX_INVENTORY:

								logger.info("Message type insert to table = "
										+ CONSTANTS.BOX_INVENTORY.TABLE_NAME);

								tableName = CONSTANTS.BOX_INVENTORY.TABLE_NAME;

								columnSet = CONSTANTS.BOX_INVENTORY.COLUMN_AMOUNT
										+ ", "
										+ CONSTANTS.BOX_INVENTORY.COLUMN_BOX_CODE
										+ ", "
										+ CONSTANTS.BOX_INVENTORY.COLUMN_MACHINE_CODE
										+ ", "
										+ CONSTANTS.BOX_INVENTORY.COLUMN_PRODUCT_CODE;

								for (int subIndex = 0; subIndex < jsonArraySubData.length(); subIndex++)
								{
									JSONObject jsonObjectSubElement = jsonArraySubData.getJSONObject(subIndex);

									time = JSONHelper.getLong(
											jsonObjectValue,
											CONSTANTS.PRODUCT_PUT_IN.COLUMN_DATE);

									if (time == 0) time = System.currentTimeMillis();

									timestamp = new Timestamp(time);

									columnValue = JSONHelper.getInt(
											jsonObjectSubElement,
											CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_AMOUNT
													+ "")

											+ ","
											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_BOX_CODE
															+ "")
											+ ", '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_MACHINE_CODE
															+ "")
											+ "', '"

											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.BOX_INVENTORY.COLUMN_INDEX_PRODUCT_CODE
															+ "") + "'";
									if (columnSet != null
											&& columnValue != null
											&& tableName != null)
									{
										logger.info("Inserting....");
										isInsertSuccess = (timestamp != null) ? DatabaseManager.insert(
												tableName, columnSet,
												columnValue,
												new Timestamp[] { timestamp }) : DatabaseManager.insert(
												tableName, columnSet,
												columnValue);
									}

									if (!isInsertSuccess)
									{
										logger.error("Insert fail!");
										// return STATUS.SERVER_DB_INSERT_FAIL;
									}
								}

								break;
							case CLIENT_TRANSACTION.PRODUCT_PUTIN:

								logger.info("Message type insert to table = "
										+ CONSTANTS.PRODUCT_PUT_IN.TABLE_NAME);

								tableName = CONSTANTS.PRODUCT_PUT_IN.TABLE_NAME;

								columnSet = CONSTANTS.PRODUCT_PUT_IN.COLUMN_AMOUNT
										+ ", "
										+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_BOX_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_DATE
										+ ", "
										+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_MACHINE_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_PRODUCT_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_STAFF_CHECKER
										+ ", "
										+ CONSTANTS.PRODUCT_PUT_IN.COLUMN_STAFF_SERVICE;

								for (int subIndex = 0; subIndex < jsonArraySubData.length(); subIndex++)
								{
									JSONObject jsonObjectSubElement = jsonArraySubData.getJSONObject(subIndex);

									time = JSONHelper.getLong(
											jsonObjectValue,
											CONSTANTS.PRODUCT_PUT_IN.COLUMN_DATE);

									if (time == 0) time = System.currentTimeMillis();

									timestamp = new Timestamp(time);

									columnValue = JSONHelper.getInt(
											jsonObjectSubElement,
											CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_AMOUNT
													+ "")

											+ ","
											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_BOX_CODE
															+ "")
											+ ", ?, '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_MACHINE_CODE
															+ "")
											+ "', '"

											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_PRODUCT_CODE
															+ "")
											+ "', '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_STAFF_CHECKER
															+ "")
											+ "', '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_STAFF_CHECKER
															+ "")

											+ "', '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_PUT_IN.COLUMN_INDEX_STAFF_SERVICE
															+ "") + "'";
									if (columnSet != null
											&& columnValue != null
											&& tableName != null)
									{
										logger.info("Inserting....");
										isInsertSuccess = (timestamp != null) ? DatabaseManager.insert(
												tableName, columnSet,
												columnValue,
												new Timestamp[] { timestamp }) : DatabaseManager.insert(
												tableName, columnSet,
												columnValue);
									}

									if (!isInsertSuccess)
									{
										logger.error("Insert fail!");
										// return STATUS.SERVER_DB_INSERT_FAIL;
									}
								}

								break;
							case CLIENT_TRANSACTION.PRODUCT_TAKEOUT:

								logger.info("Message type insert to table = "
										+ CONSTANTS.PRODUCT_TAKE_OUT.TABLE_NAME);

								tableName = CONSTANTS.PRODUCT_TAKE_OUT.TABLE_NAME;

								columnSet = CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_AMOUNT
										+ ", "
										+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_BOX_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_DATE
										+ ", "
										+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_MACHINE_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_PRODUCT_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_STAFF_CHECKER
										+ ", "
										+ CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_STAFF_SERVICE;

								for (int subIndex = 0; subIndex < jsonArraySubData.length(); subIndex++)
								{
									JSONObject jsonObjectSubElement = jsonArraySubData.getJSONObject(subIndex);

									time = JSONHelper.getLong(
											jsonObjectValue,
											CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_DATE);

									if (time == 0) time = System.currentTimeMillis();

									timestamp = new Timestamp(time);

									columnValue = JSONHelper.getInt(
											jsonObjectSubElement,
											CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_AMOUNT
													+ "")

											+ ","
											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_BOX_CODE
															+ "")
											+ ", ?, '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_MACHINE_CODE
															+ "")
											+ "', '"

											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_PRODUCT_CODE
															+ "")
											+ "', '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_STAFF_CHECKER
															+ "")
											+ "', '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_STAFF_CHECKER
															+ "")

											+ "', '"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_TAKE_OUT.COLUMN_INDEX_STAFF_SERVICE
															+ "") + "'";
									if (columnSet != null
											&& columnValue != null
											&& tableName != null)
									{
										logger.info("Inserting....");
										isInsertSuccess = (timestamp != null) ? DatabaseManager.insert(
												tableName, columnSet,
												columnValue,
												new Timestamp[] { timestamp }) : DatabaseManager.insert(
												tableName, columnSet,
												columnValue);
									}

									if (!isInsertSuccess)
									{
										logger.error("Insert fail!");
										// return STATUS.SERVER_DB_INSERT_FAIL;
									}
								}

								break;
							case CLIENT_TRANSACTION.SELL:

								logger.info("Message type insert to table = "
										+ CONSTANTS.PRODUCT_SELL.TABLE_NAME);

								// isCreateTableSuccess =
								// DatabaseUtils.createTableProductSell();

								tableName = CONSTANTS.PRODUCT_SELL.TABLE_NAME;

								columnSet = CONSTANTS.PRODUCT_SELL.COLUMN_ACCOUNT_NUMBER
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_AMOUNT
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_BOX_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_PRODUCT_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_DATE
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_IS_DELIVERIED
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_MACHINE_CODE
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_PRICE
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_WORK_NUMBER1
										+ ", "
										+ CONSTANTS.PRODUCT_SELL.COLUMN_WORK_NUMBER2;

								for (int subIndex = 0; subIndex < jsonArraySubData.length(); subIndex++)
								{
									JSONObject jsonObjectSubElement = jsonArraySubData.getJSONObject(subIndex);

									time = JSONHelper.getLong(
											jsonObjectSubElement,
											CONSTANTS.PRODUCT_SELL.COLUMN_DATE);

									if (time == 0) time = System.currentTimeMillis();

									timestamp = new Timestamp(time);

									columnValue = "'"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_ACCOUNT_NUMBER
															+ "")
											+ "',"

											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_AMOUNT
															+ "")

											+ ","
											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_BOX_CODE
															+ "")
											+ ",'"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_PRODUCT_CODE
															+ "")
											+ "', ?,"

											+ JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_IS_DELIVERIED
															+ "")
											+ ","
											+

											"'"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_MACHINE_CODE
															+ "")
											+ "',"
											+

											+JSONHelper.getInt(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_PRICE
															+ "")

											+ ",'"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_WORK_NUMBER1
															+ "")
											+ "',"

											+ "'"
											+ JSONHelper.getString(
													jsonObjectSubElement,
													CONSTANTS.PRODUCT_SELL.COLUMN_INDEX_WORK_NUMBER2
															+ "") + "'";
									if (columnSet != null
											&& columnValue != null
											&& tableName != null)
									{
										logger.info("Inserting....");
										isInsertSuccess = (timestamp != null) ? DatabaseManager.insert(
												tableName, columnSet,
												columnValue,
												new Timestamp[] { timestamp }) : DatabaseManager.insert(
												tableName, columnSet,
												columnValue);
									}

									if (!isInsertSuccess)
									{
										logger.error("Insert fail!");
										// return STATUS.SERVER_DB_INSERT_FAIL;
									}

								}

								break;

							default:
								logger.error("Wrong type update!!!!!!!");
								break;
						}

					} catch (Exception e)
					{
						e.printStackTrace();

						logger.error("Load message value fail!");
						logger.error(e.getMessage());
						return STATUS.CLIENT_SEND_FAIL_JSON_VALUE;
					}

				}
				return STATUS.CLIENT_SEND_OK;

			default:
				logger.error("Message unknow request type");
				return STATUS.CLIENT_SEND_UNKNOWN_TYPE;
		}
		if (columnSet != null && columnValue != null && tableName != null)
		{
			logger.info("Inserting....");
			isInsertSuccess = (timestamp != null) ? DatabaseManager.insert(
					tableName, columnSet, columnValue,
					new Timestamp[] { timestamp }) : DatabaseManager.insert(
					tableName, columnSet, columnValue);
		}

		if (!isCreateTableSuccess)
		{
			// logger.error("Insert fail!");
			return STATUS.SERVER_DB_CREATE_FAIL;
		}
		if (!isInsertSuccess)
		{
			logger.error("Insert fail!");
			return STATUS.SERVER_DB_INSERT_FAIL;
		}

		return STATUS.CLIENT_SEND_OK;
	}

}
